USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_scrub') BEGIN
	DROP  FUNCTION  tsql.fn_scrub
	PRINT '<<< DROP FUNCTION tsql.fn_scrub - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.fn_scrub
Type:			User defined function 
Owner:				
Description:	removes unwanted characters from given string as specified   
Parameters:		@string = string to be scrubed
				@remove_chars = list of characters to be removed (no sepparators)
Dependens on:  	none
Usage:			SELECT tsql.fn_scrub('Peterson #$',' #$')
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_scrub&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		07/31/2012 - Adrian E Dudau - Function created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE FUNCTION [tsql].[fn_scrub] 
	( @string NVARCHAR(MAX)
	, @remove_chars NVARCHAR(255)  
	)
RETURNS NVARCHAR(MAX) AS

BEGIN /*FUNCTION*/

	DECLARE @RetVal AS NVARCHAR(4000);
	DECLARE @c AS NCHAR(1);
	DECLARE @i BIGINT;
	
	SET @RetVal = @string

	-- check if valid parameters passed
	-- if not passthrough input value	
	IF LEN(ISNULL(@string,'')) = 0 
	OR LEN(ISNULL(CAST(@remove_chars AS NVARCHAR),'')) = 0 
	BEGIN
		GOTO FUNC_EXIT
	END /* IF LEN(ISNULL... */
	
	SET @i = 1
	SET @c = ''
	
	WHILE @i <= LEN(@remove_chars) BEGIN
		SET @c = SUBSTRING(@remove_chars,@i,1)
		SET @RetVal = REPLACE(@RetVal,@c,'') 
		
		SET @i = @i + 1
	END /* WHILE @i < LEN(@remove_chars) */
	
FUNC_EXIT:
	RETURN @RetVal		
END /*FUNCTION*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'fn_scrub') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_scrub - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_scrub - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

-- GRANT SELECT TO PUBLIC will be used at Db level



